﻿using System;
using System.Collections.Generic;
using System.Data;
using Framework.Extensions;
using System.Linq;

namespace Framework.Sql
{
    public class SelectSqlBuilder : SqlBuilderBase
    {
        private readonly List<string> _columnNames = new List<string>();
        public SelectSqlBuilder(string connectionString, string tableName) : base(connectionString, tableName) { }
        private readonly List<SelectSqlBuilder> _unionedBuilders = new List<SelectSqlBuilder>();

        public IEnumerable<string> ColumnNames
        {
            get { return _columnNames; }
        }

        public void AddSelectColumn(string columnName)
        {
            _columnNames.Add(columnName);
        }

        public void Union(params SelectSqlBuilder[] builder)
        {
            _unionedBuilders.AddRange(builder);
        }

        public int? PageCount { get; set; }
        public int? PageNumber { get; set; }
        public string PageSortColumn { get; set; }

        private readonly List<string> _sortColumns = new List<string>();

        public void AddSortColumn(string columnName, bool desc)
        {
            _sortColumns.Add(string.Format("[{0}] {1}", columnName, desc ? "desc" : "asc"));
            if (!_columnNames.Contains(columnName))
                _columnNames.Add(columnName);
        }

        public IEnumerable<T> ExecuteSql<T>(Func<IDataRecord, T> fn)
        {
            var sql = new[] {GetSql()}
                .Concat(
                    _unionedBuilders
                        .Select(b => b.GetSql()))
                .SeparateBy(" union all ");

            if (PageSortColumn != null)
            {
                var sortString = _sortColumns.SeparateBy(", ");
                var columnList = _columnNames.Select(c => c[0] == '\'' ? "col1" : "[" + c + "]").SeparateBy(", ");

                sql = string.Format("select {0}, row_number() over (order by {2}) as row_num from ({1}) t",
                    columnList, sql, sortString);

                var pageCount = PageCount != null ? PageCount.Value : 10;
                var pageNumber = PageNumber != null ? PageNumber.Value : 1;
                sql = string.Format("with sort_table ({0}, row_num) as ({1}) select * from sort_table where row_num between {2} and {3} order by {4}",
                    columnList, sql, pageCount * (pageNumber - 1) + 1, pageCount * pageNumber, sortString);
            }

            return new SqlExecutor(ConnectionString).Exec(sql, fn);
        }

        private string GetSql()
        {
            var columnList = _columnNames.Select(c => c[0] == '\'' ? c + " as col1" : "[" + c + "]").SeparateBy(", ");

            return string.Format("select {0} from [{1}]{2}",
                                 columnList,
                                 TableName,
                                 Conditions.Any() ? (" where " + Conditions.SeparateBy(" and ")) : string.Empty);
        }

        public void AddCondition(string conditionSql)
        {
            Conditions.Add(conditionSql);
        }
    }
}